﻿Imports System.Data.OleDb

Public Class Users_RankingPointsManagement
    Inherits ConnectionClass

    Public Function SelectRankingPointsByType(ByVal pointsReasons As String) As Int32
        Try


            myConnection.Open()
            Dim SqlString As String = "SELECT RankingPointId FROM   (RankingPoints) WHERE ( RankingPointReason= @f1)"
            Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@f1", pointsReasons)
            Dim dr As OleDbDataReader = cmd.ExecuteReader()
            dr.Read()
            If (dr.HasRows) Then
                Dim myValue As Int32 = dr.GetValue(0)
                myConnection.Close()
                Return myValue
            Else
                myConnection.Close()
                Return 0
            End If

        Catch ex As Exception
            Return Nothing
        End Try

    End Function

    Public Sub InsertUsersRankingPoints(ByVal userId As Int32, ByVal RankingPointId As Int32)
        Try


        myConnection.Open()
        Dim SqlString As String = "INSERT INTO Users_RankingPoints(UserId,RankingPointId) VALUES(@f1,@f2)"
        Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        newCmd.CommandType = CommandType.Text
        newCmd.Parameters.AddWithValue("@f1", userId)
        newCmd.Parameters.AddWithValue("@f2", RankingPointId)
        newCmd.ExecuteNonQuery()
        myConnection.Close()
        Catch ex As Exception

        End Try
    End Sub

    Public Function SelectRankingPointsByRankingPointId(ByVal rankingPointId As Int32) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "Select * From RankingPoints Where RankingPointId=@f1"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", rankingPointId)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()

        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Sub UpdateRankingPointsByRankingPointId(ByVal RankingPointId As Int32, ByVal RankingPointValue As Int32)
        Try


            myConnection.Open()

            Dim SqlString As String = "UPDATE   RankingPoints Set  RankingPointValue=@f1 where RankingPointId = @f2"
            Dim newCmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
            newCmd.CommandType = CommandType.Text
            newCmd.Parameters.AddWithValue("@f1", RankingPointValue)
            newCmd.Parameters.AddWithValue("@f2", RankingPointId)

            newCmd.ExecuteNonQuery()
            myConnection.Close()

        Catch ex As Exception

        End Try
    End Sub

    Public Function SelectSumRankingPointsByUserId(ByVal userId As Int32) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT   RankingPoints.RankingPointReason, SUM(RankingPoints.RankingPointValue) AS TotalPoints, RankingPoints.RankingPointValue FROM            (Users_RankingPoints INNER JOIN RankingPoints ON Users_RankingPoints.RankingPointId = RankingPoints.RankingPointId) WHERE   (Users_RankingPoints.UserId = @f1) GROUP BY RankingPoints.RankingPointReason, RankingPoints.RankingPointValue"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", userId)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function


    Public Function SelectSumRankingPointsTotalByUserId(ByVal userId As Int32) As OleDbDataReader
        Try

        
        myConnection.Open()
        Dim SqlString As String = "SELECT   SUM(RankingPoints.RankingPointValue) AS TotalPoints FROM (Users_RankingPoints INNER JOIN RankingPoints ON Users_RankingPoints.RankingPointId = RankingPoints.RankingPointId) WHERE        (Users_RankingPoints.UserId = @f1)"
        Dim cmd As OleDbCommand = New OleDbCommand(SqlString, myConnection)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@f1", userId)
        Dim dr As OleDbDataReader = cmd.ExecuteReader()
        Return dr
        myConnection.Close()
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

End Class
